package cn.edu.swu.book;

import cn.edu.swu.core.AbstractRepo;
import cn.edu.swu.core.ResultSetVisitor;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class BookRepo extends AbstractRepo {
    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");

    public List<Book> getAllBooks() throws SQLException, ClassNotFoundException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books;
    }

    public void save(Book book) throws SQLException {
        if (book.getId() < 0) {
            this.add(book);
        } else {
            this.update(book);
        }
    }

    public void add(Book book) throws SQLException {

        String template = "INSERT INTO book (name, author, price, memo, publish, image) " +
                "values ('%s', '%s', %s, '%s', '%s', '%s')";
        this.execute(String.format(template, book.getName(), book.getAuthor(), book.getPrice(),
                book.getMemo(), dateFormat.format(book.getPublish()), book.getImage()));
    }

    public void update(Book book) throws SQLException {
        String template = "UPDATE book " +
                "SET name='%s', author='%s', price=%s, memo='%s', publish='%s', image='%s' " +
                "WHERE id=%s";
        this.execute(String.format(template, book.getName(), book.getAuthor(), book.getPrice(),
                book.getMemo(), dateFormat.format(book.getPublish()), book.getImage(), book.getId()));
    }

    public void deleteById(int id) throws SQLException {
        String template = "DELETE FROM book WHERE id = %s";
        this.execute(String.format(template, id));
    }

    public Book getById(int id) throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE id=" + id;
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books.size() > 0 ? books.get(0) : null;
    }

    public List<Book> search(String key) throws SQLException {
        List<Book> books = new ArrayList<>();
        // 增加publish和price字段的模糊查询条件
        String sql = "SELECT * FROM book WHERE name LIKE '%" + key + "%' " +
                "OR author LIKE '%" + key + "%' " +
                "OR publish LIKE '%" + key + "%' " +
                "OR price LIKE '%" + key + "%'";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books;
    }

    public List<Book> searchByPriceRange(Double minPrice, Double maxPrice) throws SQLException {
        if (minPrice == null || maxPrice == null) {
            throw new SQLException("价格范围参数不能为null");
        }

        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE price BETWEEN " + minPrice + " AND " + maxPrice;

        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });

        return books;
    }


    private Book getOneRecord(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getInt("id"));
        book.setName(rs.getString("name"));
        book.setAuthor(rs.getString("author"));
        book.setPrice(rs.getDouble("price"));
        book.setMemo(rs.getString("memo"));
        book.setPublish(rs.getDate("publish"));
        book.setImage(rs.getString("image"));
        return book;
    }

    // 添加购物车条目
    public void addToCart(CartItem cartItem) throws SQLException {
        String template = "INSERT INTO user_cart (user_id, book_id, quantity) VALUES (%s, %s, %s)";
        this.execute(String.format(template, cartItem.getUserId(), cartItem.getBookId(), cartItem.getQuantity()));
    }

    // 查询用户购物车内容
    public List<CartItem> getCartItemsByUserId(int userId) throws SQLException {
        List<CartItem> cartItems = new ArrayList<>();
        String sql = "SELECT * FROM user_cart WHERE user_id=" + userId;
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                cartItems.add(new CartItem(
                        rs.getInt("user_id"),
                        rs.getInt("book_id"),
                        rs.getInt("quantity")
                ));
            }
        });
        return cartItems;
    }

    // 更新购物车中的商品数量
    public void updateCartItemQuantity(int userId, int bookId, int quantity) throws SQLException {
        String template = "UPDATE user_cart SET quantity=%s WHERE user_id=%s AND book_id=%s";
        this.execute(String.format(template, quantity, userId, bookId));
    }

    // 从购物车中删除商品
    public void removeFromCart(int userId, int bookId) throws SQLException {
        String template = "DELETE FROM user_cart WHERE user_id=%s AND book_id=%s";
        this.execute(String.format(template, userId, bookId));
    }

    public List<Book> searchByName(String bookName) throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE name LIKE '%" + bookName + "%'";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books;
    }

    public List<CartItem> getCartItemsByUserIdAndBookId(int userId, int bookId) throws SQLException {
        List<CartItem> cartItems = new ArrayList<>();
        String sql = "SELECT * FROM user_cart WHERE user_id=" + userId + " AND book_id=" + bookId;
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                cartItems.add(new CartItem(
                        rs.getInt("user_id"),
                        rs.getInt("book_id"),
                        rs.getInt("quantity")
                ));
            }
        });
        return cartItems;
    }

}
